CREATE  VIEW [dbo].[vFullAddressPrimaryReport]
AS
/****************************************************************************************
** This view provides Primary Full Address information for each Contact on "One Row per Contact" basis.
** This can be joined to other  relevant tables or views to get the necessary address related information..
**
***************************************************************************************/
SELECT    
     AM1.FormattedAddress AS PrimaryPhysicalAddress, AM2.FormattedAddress AS PrimaryDayTimePhone,  AM3.FormattedAddress AS PrimaryEveningPhone,
    AM4.FormattedAddress AS PrimaryFax, AM5.FormattedAddress AS PrimaryEmail, FA.FullAddressDesc AS FullAddressPurpose, PA.Address1, PA.Address2, PA.Address3,
    PA.City, PA.StateProvinceCode, PA.Region, PA.PostalCode, PA.CountryCode, PA.County, PA.MailCode, PA.CarrierRoute, PA.DeliveryPointBarCode, PA.LineOfTravel,
    PA.BarCode, PA.USCongress, PA.StateSenate, PA.Latitude, PA.Longitude, SPR.StateProvinceDesc, CR.CountryName, CCRP.PreferredAddressCategoryCode,
    ACR.AddressCategoryName AS PreferredAddressCategory, CRR.CommunicationReasonDesc, CRR.IsDefaultType, FA.FullAddressKey, FA.ContactKey,
    FA.SalutationKey, ATU.SeasonalRevertFullAddressKey

FROM         
    FullAddress     FA
    LEFT OUTER JOIN     PhysicalAddress     PA     ON     FA.PhysicalAddressKey = PA.AddressKey
    LEFT OUTER JOIN     AddressMain         AM1     ON     FA.PhysicalAddressKey = AM1.AddressKey  
    LEFT OUTER JOIN     AddressMain         AM2     ON     FA.DaytimePhoneAddressKey = AM2.AddressKey
    LEFT OUTER JOIN     AddressMain         AM3     ON     FA.EveningPhoneAddressKey = AM3.AddressKey
    LEFT OUTER JOIN     AddressMain         AM4     ON     FA.FaxAddressKey = AM4.AddressKey
    LEFT OUTER JOIN     AddressMain         AM5     ON     FA.EmailAddressKey = AM5.AddressKey
    LEFT OUTER JOIN     AddressToUse         ATU     ON     FA.FullAddressKey = ATU.FullAddressKey
    LEFT OUTER JOIN     StateProvinceRef     SPR     ON     PA.StateProvinceCode = SPR.StateProvinceCode
    LEFT OUTER JOIN     CountryRef         CR     ON     PA.CountryCode = CR.CountryCode
      LEFT OUTER JOIN     CommunicationReasonRef CRR     ON     ATU.CommunicationReasonKey = CRR.CommunicationReasonKey
     LEFT OUTER JOIN   ContactCommunicationReasonPreferences CCRP ON ATU.CommunicationReasonKey = CCRP.CommunicationReasonKey
                    AND ATU.ContactKey = CCRP.ContactKey
      LEFT OUTER JOIN     AddressCategoryRef     ACR     ON     CCRP.PreferredAddressCategoryCode = ACR.AddressCategoryCode
    
    WHERE  CRR.IsDefaultType = 1

GO